package com.refusea.train.mapper

import com.refusea.train.domain.entity.User
import com.refusea.train.domain.entity.User.Role
import org.apache.ibatis.annotations.Delete
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Options
import org.apache.ibatis.annotations.Param
import org.apache.ibatis.annotations.Select
import org.apache.ibatis.annotations.Update
import java.time.LocalDate
import java.time.LocalDateTime

@Mapper
interface UserMapper {

    @Insert(
        """
        INSERT ignore INTO user (school_id, name, login_id, password, role, referrer_id, parent_id) 
        VALUES 
        (#{schoolId}, #{name}, #{loginId}, #{password}, #{role}, #{referrerId}, #{parentId})
        """
    )
    @Options(useGeneratedKeys = true, keyProperty = "id")
    fun insert(user: User): Int

    @Delete("DELETE FROM user WHERE id = #{id}")
    fun delete(id: Int): Int

    @Update(
        """
        UPDATE user SET 
            school_id = #{schoolId}, name = #{name}, login_id = #{loginId}, 
            password = #{password}, role = #{role}
        WHERE id = #{id}
    """
    )
    fun update(user: User): Int

    @Select("SELECT * FROM user WHERE id = #{id}")
    fun select(id: Int): User?

    @Select("SELECT * FROM user where school_id = #{schoolId} and role = #{role}")
    fun list(@Param("schoolId") schoolId: Int, @Param("role") role: Role): List<User>

    @Select("SELECT * FROM user WHERE login_id=#{loginId}")
    fun selectByLoginId(loginId: String): User?

    @Update("UPDATE user SET name=#{name} WHERE id=#{id}")
    fun updateName(@Param("id") id: Int, @Param("name") name: String): Int

    @Update("UPDATE user SET password=#{newPassword} WHERE id=#{id}")
    fun updatePassword(id: Int, newPassword: String): Int

    @Select("SELECT * FROM user WHERE school_id=#{schoolId} and role in ('PRINCIPAL','TEACHER')")
    fun teachers(schoolId: Int): List<User>

    @Select("SELECT * FROM user WHERE school_id=#{schoolId} and role in ('PARENT','STUDENT')")
    fun students(schoolId: Int): List<User>

    @Select("SELECT * FROM user")
    fun listAll(): List<User>

    @Select("SELECT id FROM user WHERE parent_id = #{id} and role='STUDENT'")
    fun childrenIds(id: Int): List<Int>

    @Select("SELECT * FROM user WHERE school_id = #{schoolId} and parent_id = #{id} and role='STUDENT'")
    fun children(@Param("schoolId") schoolId: Int, @Param("id") id: Int): List<User>

    @Select(
        """
        <script>
        SELECT * FROM user WHERE referrer_id = #{referrerId} and role='PARENT'
        <if test="start!= null">and ct > #{start}</if>
        and ct &lt; #{end}
        </script>
        """
    )
    fun listByReferrer(
        @Param("referrerId") referrerId: Int,
        @Param("start") start: LocalDateTime?,
        @Param("end") end: LocalDate,
    ): List<User>

    @Select("SELECT * FROM user WHERE referrer_id = #{id} and role='PARENT'")
    fun listAllByReferrer(id: Int): List<User>
}
